package com.admission.service;

import java.io.File;
import java.io.IOException;
import java.util.List;

import org.apache.commons.lang.xwork.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;
import org.xml.sax.SAXException;

import com.admission.dao.RecruitmentDetailDao;
import com.admission.model.RecruitmentDetail;
import com.admission.util.ArrayUtils;
import com.admission.util.DeleteFile;
import com.admission.util.EnrollUtils;
import com.admission.util.ExcelUtils;
import com.admission.util.FileFilter;
import com.admission.util.StaticRecruitment;

@Repository
@Transactional
public class RecruitmentDetailManager {

	private RecruitmentDetailDao recruitmentDetailDao;
	private StaticRecruitment staticRecruitment;
	private ArrayUtils arrayUtils;

	public RecruitmentDetail getById(String id) {
		return recruitmentDetailDao.get(id);
	}

	public Page<RecruitmentDetail> getPage(Page<RecruitmentDetail> page) {
		return recruitmentDetailDao.getAll(page);
	}

	public List<String> getYears() {
		return recruitmentDetailDao
				.find("select year from RecruitmentDetail group by year");
	}

	/**
	 * 获取年份，省份，类型的组合
	 * 
	 * @param page
	 * @return
	 */
	public Page<RecruitmentDetail> getTitleList(Page<RecruitmentDetail> page) {
		List<RecruitmentDetail> l = recruitmentDetailDao
				.find("from RecruitmentDetail group by year,province,type order by year desc ");
		if (page.getPageSize() * page.getPageNo() > l.size()) {
			page.setResult(l.subList(page.getPageSize()
					* (page.getPageNo() - 1), l.size()));
		} else
			page.setResult(l.subList(page.getPageSize()
					* (page.getPageNo() - 1),
					page.getPageSize() * page.getPageNo()));
		page.setTotalCount(l.size());
		return page;
	}

	public void delete(String path, String year, String province, String type)
			throws IOException, IllegalArgumentException, SAXException, IllegalAccessException {
		Criterion criterions = Restrictions.eq("year", year);
		Criterion criterions1 = Restrictions.eq("province", province);
		Criterion criterions2 = Restrictions.eq("type", type);
		List<RecruitmentDetail> l = null;
		if (StringUtils.isEmpty(province) || StringUtils.isEmpty(type)) {
			l = recruitmentDetailDao.find(criterions);
		} else {
			l = recruitmentDetailDao.find(criterions, criterions1, criterions2);
		}

		for (RecruitmentDetail recruitmentDetail : l) {
			recruitmentDetailDao.delete(recruitmentDetail);
		}
		File f = new File(path);
		// 删除静态网页
		String regx = "recruitment-detail-" + year + "-" + province + "-"
				+ type + ".*";
		File[] s = f.listFiles(new FileFilter(regx));
		for (File file : s) {
			DeleteFile.deleteByFile(file);
		}

		// 重新生成静态页面
		staticRecruitment.createHtmlForRecruitmentDetail();
	}

	public void save(String path, String uploadType) throws Exception {
		Workbook workbook = ExcelUtils.getCommonWorkbook(path);

		Sheet sheet = workbook.getSheetAt(0);
		// 其他类型
		if (!uploadType.equals("1")) {
			for (int j = 1; j <= sheet.getLastRowNum(); j++) {
				RecruitmentDetail rd = new RecruitmentDetail();
				Row row1 = sheet.getRow(j);
				Cell cell = row1.getCell((short) 0);
				rd.setSequence(EnrollUtils.getCellFormatValue(cell));
				rd.setYear(EnrollUtils.getCellFormatValue(row1.getCell(1)));
				rd.setProvince((String) arrayUtils.getKey(
						arrayUtils.getProvinceMap(),
						EnrollUtils.getCellFormatValue(row1.getCell(2))));
				rd.setType((String) arrayUtils.getKey(arrayUtils.getType(),
						EnrollUtils.getCellFormatValue(row1.getCell(3))));
				cell = row1.getCell((short) 4);
				if (StringUtils.isEmpty(rd.getProvince())) {
					throw new Exception("录取统计表格第" + (j + 1) + "行的省份有错误，录取统计提交失败");
				}
				if (StringUtils.isEmpty(rd.getType())) {
					throw new Exception("录取统计表格第" + (j + 1) + "行的类型有错误，录取统计提交失败");
				}
				rd.setAcademy(EnrollUtils.getCellFormatValue(cell));
				cell = row1.getCell((short) 5);
				rd.setMajor(EnrollUtils.getCellFormatValue(cell));
				cell = row1.getCell((short) 6);
				rd.setEnrollNum(EnrollUtils.getCellFormatValue(cell));
				cell = row1.getCell((short) 7);
				rd.setHightScore(EnrollUtils.getCellFormatValue(cell));
				cell = row1.getCell((short) 8);
				rd.setAvgScore(EnrollUtils.getCellFormatValue(cell));
				cell = row1.getCell((short) 9);
				rd.setLowScore(EnrollUtils.getCellFormatValue(cell));
				cell = row1.getCell((short) 10);
				rd.setContent(EnrollUtils.getCellFormatValue(cell));
				recruitmentDetailDao.save(rd);
			}
		} else {
			for (int j = 1; j <= sheet.getLastRowNum(); j++) {
				RecruitmentDetail rd = new RecruitmentDetail();
				Row row1 = sheet.getRow(j);
				Cell cell = row1.getCell((short) 0);
				rd.setSequence(EnrollUtils.getCellFormatValue(cell));
				rd.setYear(EnrollUtils.getCellFormatValue(row1.getCell(1)));
				rd.setProvince((String) arrayUtils.getKey(
						arrayUtils.getProvinceMap(),
						EnrollUtils.getCellFormatValue(row1.getCell(2))));
				rd.setType((String) arrayUtils.getKey(arrayUtils.getType(),
						EnrollUtils.getCellFormatValue(row1.getCell(3))));
				if (StringUtils.isEmpty(rd.getProvince())) {
					throw new Exception("录取统计表格第" + (j + 1) + "行的省份有错误，录取统计提交失败");
				}
				if (StringUtils.isEmpty(rd.getType())) {
					throw new Exception("录取统计表格第" + (j + 1) + "行的类型有错误，录取统计提交失败");
				}
				cell = row1.getCell((short) 4);
				rd.setAcademy(EnrollUtils.getCellFormatValue(cell));
				cell = row1.getCell((short) 5);
				rd.setMajor(EnrollUtils.getCellFormatValue(cell));
				cell = row1.getCell((short) 6);
				rd.setEnrollNum(EnrollUtils.getCellFormatValue(cell));
				cell = row1.getCell((short) 7);
				rd.setLowScoreCulture(EnrollUtils.getCellFormatValue(cell));
				cell = row1.getCell((short) 8);
				rd.setLowScoreSubject(EnrollUtils.getCellFormatValue(cell));
				cell = row1.getCell((short) 9);
				rd.setContent(EnrollUtils.getCellFormatValue(cell));
				recruitmentDetailDao.save(rd);
			}
		}
		DeleteFile.delete(path);// 删除excel
		File f = new File(path).getParentFile().getParentFile();// 删除已存在静态页面
		f = new File(f.getAbsolutePath() + File.separator + "/statichtml");
		String regx = "recruitment-detail-.*";
		File[] s = f.listFiles(new FileFilter(regx));
		for (File file : s) {
			DeleteFile.deleteByFile(file);
		}
		try {
			staticRecruitment.createHtmlForRecruitmentDetail();
		} catch (Exception e) {
			throw new Exception("录取统计静态页面生成失败，可能数据库中存在不合法字段，具体情况，请联系开发人员调试"
					+ e.toString());
		}
	}

	public List<String> yearList() {
		return recruitmentDetailDao
				.find("select distinct rd.year from RecruitmentDetail rd order by rd.year desc");
	}

	public List<String> typeList() {
		return recruitmentDetailDao
				.find("select distinct rd.type from RecruitmentDetail rd");
	}

	public List<String> provinceList() {
		return recruitmentDetailDao
				.find("select distinct rd.province from RecruitmentDetail rd");
	}

	@Autowired
	public void setStaticRecruitment(StaticRecruitment staticRecruitment) {
		this.staticRecruitment = staticRecruitment;
	}

	@Autowired
	public void setRecruitmentDetailDao(
			RecruitmentDetailDao recruitmentDetailDao) {
		this.recruitmentDetailDao = recruitmentDetailDao;
	}

	@Autowired
	public void setArrayUtils(ArrayUtils arrayUtils) {
		this.arrayUtils = arrayUtils;
	}

}
